﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using DTO;
using System.Data.SqlClient;
namespace DAO
{
    public class GetThongTinSearchDAO
    {
        public static DataTable SearchNgoaiTru(GetThongTinSearchDTO tt, SqlConnection conpk)
        {
            DataTable dtKQ = new DataTable();
            string sp = "usp_GPB_GetDsBenhNhanNgTru ";
            sp += String.Format(" '{0}','{1}'",
                                tt.STuNgay, tt.SDenNgay);
            dtKQ = Uit.it_SQL.getDataTable(sp,conpk);
            return dtKQ;
        }

        public static DataTable GetMDDungDichCD()
        {
            DataTable dtKQ = new DataTable();
            string sp = "GetMDDungDichCD ";            
            dtKQ = Uit.it_SQL.getDataTable(sp);
            return dtKQ;
        }

        public static DataTable SearchTuongTrinh(string SoHS, int LanMo, SqlConnection conpk)
        {
            DataTable dtKQ = new DataTable();
            string sp = "usp_GPB_GetTuongTrinh ";
            sp += String.Format(" '{0}',{1}",
                                SoHS, LanMo);
            dtKQ = Uit.it_SQL.getDataTable(sp,conpk);
            return dtKQ;
        }

        public static Boolean InsertTuongTrinh(string SoPT, int LanMo
                                                ,string TuongTrinh, SqlConnection conpk)
        {
            Boolean dtKQ =false;
            string sp = "usp_GPB_InsertTuongTrinh ";
            sp += String.Format(" '{0}',{1},N'{2}'",
                                SoPT, LanMo, TuongTrinh);
            dtKQ = Uit.it_SQL.ExecuteNonQuery(sp,conpk);
            return dtKQ;
        }

        public static Boolean UpdateTuongTrinh(string SoPT, int LanMo
                                                , string TuongTrinh, SqlConnection conpk)
        {
            Boolean dtKQ = false;
            string sp = "usp_GPB_UpdateTuongTrinh ";
            sp += String.Format(" '{0}',{1},N'{2}'",
                                SoPT, LanMo, TuongTrinh);
            dtKQ = Uit.it_SQL.ExecuteNonQuery(sp, conpk);
            return dtKQ;
        }

        public static DataTable SearchNoiTru(GetThongTinSearchDTO tt, SqlConnection conpk)
        {
            DataTable dtKQ = new DataTable();
            string sp = "usp_GPB_GetDsBenhNhanNoiTru ";
            sp += String.Format(" '{0}','{1}'",
                                tt.STuNgay, tt.SDenNgay);
            dtKQ = Uit.it_SQL.getDataTable(sp,conpk);
            return dtKQ;
        }

        public static DataTable SearchChuongTrinh(GetThongTinSearchDTO tt
                                , SqlConnection conpk, int loaibn)
        {
            DataTable dtKQ = new DataTable();
            string sp = "usp_GPB_GetDsBenhNhanCT_MG ";
            sp += String.Format(" '{0}','{1}',{2}",
                                tt.STuNgay, tt.SDenNgay, loaibn);
            dtKQ = Uit.it_SQL.getDataTable(sp, conpk);
            return dtKQ;
        }

        public static DataTable SearchKSK(GetThongTinSearchDTO tt, SqlConnection conpk)
        {
            DataTable dtKQ = new DataTable();
            string sp = "usp_GPB_GetDsBenhNhanKSK ";
            sp += String.Format(" '{0}','{1}'",
                                tt.STuNgay, tt.SDenNgay);
            dtKQ = Uit.it_SQL.getDataTable(sp,conpk);
            return dtKQ;
        }
       
        /// <summary>
        /// Get DS da xac nhan
        /// </summary>
        /// <param name="tungay">tu ngay</param>
        /// <param name="denngay">den ngay</param>
        /// <param name="nLoaiBN">loai benh nhan</param>
        /// <param name="kq">0: chua co kq; 1: da co kq</param>        
        /// <returns></returns>
        public static DataTable SearchDaXacNhan(string tungay, string denngay,
                                                int nLoaiBN, int kq)
        {
            DataTable dtKQ = new DataTable();
            string sp = "usp_GPB_GetDsDaNhan ";
            sp += String.Format(" '{0}','{1}',{2},{3},{4}",
                                tungay, denngay, nLoaiBN,kq,1);
            dtKQ = Uit.it_SQL.getDataTable(sp);
            return dtKQ;
        }

        public static DataTable SearchDaXacNhanBySoTB(string SoTieuBan)
        {
            DataTable dtKQ = new DataTable();
            string sp = "usp_GPB_GetDsDaNhanBySoTB ";
            sp += String.Format(" '{0}'", SoTieuBan);
            dtKQ = Uit.it_SQL.getDataTable(sp);

            return dtKQ;
        }
        public static DataTable GetDSPPNhuom()
        {
            DataTable dtKQ = new DataTable();
            string sp = "SELECT [IDPPNhuom]				   \n"
                        + "      ,[TenPPNhuom]				   \n"
                        + "FROM [dbo].[GPB_DM_PPNHUOM]		   \n"
                        + "WHERE [TinhTrang] = 0 \n";
            dtKQ = Uit.it_SQL.getDataTable(sp);

            return dtKQ;
        }


        public static DataTable GetChiDinhThem(string SoTieuBan)
        {
            DataTable dtKQ = new DataTable();
            string sp = "usp_GPB_GetChiDinhThem ";
            sp += String.Format(" '{0}'", SoTieuBan);
            dtKQ = Uit.it_SQL.getDataTable(sp);

            return dtKQ;
        }

        public static DataTable GetChiDinhHoanTien(string SoTieuBan)
        {
            DataTable dtKQ = new DataTable();
            string sp = "usp_GPB_GetChiDinhHoanTien ";
            sp += String.Format(" '{0}'", SoTieuBan);
            dtKQ = Uit.it_SQL.getDataTable(sp);

            return dtKQ;
        }

        public static DataTable SearchPhanLoaiDV(string DVuCode)
        {
            DataTable dtKQ = new DataTable();
            string sp = "usp_GPB_GetDV ";
            sp += String.Format(" '{0}'", DVuCode);
            dtKQ = Uit.it_SQL.getDataTable(sp);

            return dtKQ;
        }

        public static DataTable GetChiDinh(string SoHS,string SoBN, string DVuCode)
        {
            DataTable dtKQ = new DataTable();
            string sp = "usp_GPB_GetChiDinh ";
            sp += String.Format(" '{0}','{1}','{2}'", SoHS, SoBN, DVuCode);
            dtKQ = Uit.it_SQL.getDataTable(sp);

            return dtKQ;
        }

        public static DataTable GetChiDinhKhamBenh(string SoBN, string SoHS , string DVCode, SqlConnection conpk)
        {
            DataTable dtKQ = new DataTable();
            string sp = "usp_GPB_GetChiDinhKhamBenh ";
            sp += String.Format(" '{0}','{1}','{2}'", SoBN, SoHS, DVCode);
            dtKQ = Uit.it_SQL.getDataTable(sp, conpk);

            return dtKQ;
        }

        public static DataTable GetChiDinhCLS(string SoBN, string SoHS, SqlConnection conpk)
        {
            DataTable dtKQ = new DataTable();
            string sp = "SELECT TOP 1 *                         \n"
                    + "FROM                                     \n"
                    + "	[HIS].[dbo].[Utb_PhieuChiDinh_Mst]      \n"
                    + "WHERE                                    \n"
                    + "	SoHoSo ='" + SoHS + "'                  \n"
                    + "	AND MaPhieu ='" + SoBN + "'             \n";
           
            dtKQ = Uit.it_SQL.getDataTable(sp, conpk);

            return dtKQ;
        }

        public static DataTable SearchDaXacNhanKSK(string tungay, string denngay,
                                               int nLoaiBN, int kq)
        {
            DataTable dtKQ = new DataTable();
            string sp = "usp_GPB_GetDsDaNhanKSK ";
            sp += String.Format(" '{0}','{1}',{2},{3}",
                                tungay, denngay, nLoaiBN, kq);
            dtKQ = Uit.it_SQL.getDataTable(sp);
            return dtKQ;
        }

        public static DataTable SearchDSBacSi( )
        {
            DataTable dtKQ = new DataTable();
            string sp = "usp_GPB_GetDsBSi ";
            dtKQ = Uit.it_SQL.getDataTable(sp);
            return dtKQ;
        }

        public static DataTable GetHMMD()
        {
            DataTable dtKQ = new DataTable();
            string sp = "usp_GPB_GetHMMD ";
            dtKQ = Uit.it_SQL.getDataTable(sp);
            return dtKQ;
        }

        public static DataTable GetNhuomDB()
        {
            DataTable dtKQ = new DataTable();
            string sp = "usp_GPB_GetNhuomDB ";
            dtKQ = Uit.it_SQL.getDataTable(sp);
            return dtKQ;
        }

        public static DataTable GetDMHoanTien()
        {
            DataTable dtKQ = new DataTable();
            string sp = "usp_GPB_DMHoanTien ";
            dtKQ = Uit.it_SQL.getDataTable(sp);
            return dtKQ;
        }

        public static DataTable SearchDSBacSiCD()
        {
            DataTable dtKQ = new DataTable();
            string sp = "usp_GPB_GetDsBSiCD ";
            dtKQ = Uit.it_SQL.getDataTable(sp);
            return dtKQ;
        }

        public static DataTable SearchDSKTV()
        {
            DataTable dtKQ = new DataTable();
            string sp = "usp_GPB_GetDsKTV ";
            dtKQ = Uit.it_SQL.getDataTable(sp);
            return dtKQ;
        }

        public static DataTable SearchPPMo( )
        {
            DataTable dtKQ = new DataTable();
            string sp = "usp_GPB_GetPPMo ";
            dtKQ = Uit.it_SQL.getDataTable(sp);
            return dtKQ;
        }

        public static DataTable SearchDonViGuiMau()
        {
            DataTable dtKQ = new DataTable();
            string sp = "usp_GPB_DonViGuiMau ";
            dtKQ = Uit.it_SQL.getDataTable(sp);
            return dtKQ;
        }

        public static DataTable SearchDonVi(SqlConnection conpk)
        {
            DataTable dt1 = new DataTable();
            DataTable dt2 = new DataTable();
            DataTable dtKQ = new DataTable();
            string sp = "  SELECT                                 \n"
                        + "	dv.MaDonVi                          \n"
                        + "	,dv.TenDonVi                        \n"
                        + "FROM his.dbo.utb_DM_DonVi AS dv      \n"
                        + "WHERE dv.NgungSD = 0                 \n"
                        + "UNION ALL                            \n"
                        + "SELECT                               \n"
                        + "	CAST( dv.RID AS  NVARCHAR(10))	AS	MaDonVi \n"
                        + "	,dv.MoTa	AS	TenDonVi            \n"
                        + "FROM ksk.dbo.DM_DonViTaiTro AS dv	\n"
                        + "WHERE                                \n"
                        + " dv.NgungDung = 0                    \n"
                        + " AND dv.RID = 14                     ";
            dt1 = Uit.it_SQL.getDataTable(sp, conpk);

            string sp2 = "SELECT                          \n"
                        + "	[MaDonVi]                     \n"
                        + "	,[TenDonVi]                   \n"                       
                        + "FROM                            \n"
                        + "	[SANNHI].[dbo].[GPB_DVGUIMAU] \n"
                        + "WHERE                           \n"
                        + "	LoaiDV =2                     \n"
                        + "	AND NgungSD =0                \n";
            dt2 = Uit.it_SQL.getDataTable(sp2);
            dt1.Merge(dt2);
            return dt1;
        }

        public static DataTable GetDsBenhPAP()
        {
            DataTable dtKQ = new DataTable();
            string sp = "usp_GPB_GetDsBenhPAP ";
            dtKQ = Uit.it_SQL.getDataTable(sp);
            return dtKQ;
        }

        public static DataTable GetURLHinh(SqlConnection conpk)
        {
            DataTable dtKQ = new DataTable();
            string sp = "SELECT                        \n"
                        + "	[MaThamSo]                  \n"
                        + "    ,[MoTa]                   \n"
                        + "    ,[GiaTri]                 \n"
                        + "    ,[ChuongTrinh]            \n"
                        + "    ,[GhiChu]                 \n"
                        + "FROM                          \n"
                        + "	[HIS].[dbo].[utb_SYS_ThamSo]\n"
                        + "WHERE                         \n"
                        + "	MaThamSo ='GPB_URLChupHinh' \n";
            dtKQ = Uit.it_SQL.getDataTable(sp, conpk);
            return dtKQ;
        }
    }
}
